import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

interface DatabaseConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  waitForConnections: boolean;
  connectionLimit: number;
  queueLimit: number;
}

const config: DatabaseConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: Number(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'dimensional_echo',
  waitForConnections: true, // 当连接池没有可用连接时，是否等待（而不是抛出错误）
  connectionLimit: 10, // 连接池的最大连接数
  queueLimit: 0, // 等待队列的最大长度（0表示不限制）
};

// 创建连接池
const pool = mysql.createPool(config);

// 测试数据库连接
export const testConnection = async (): Promise<boolean> => {
  try {
    const connection = await pool.getConnection();
    await connection.ping();
    connection.release();
    console.log('数据库连接成功');
    return true;
  } catch (error) {
    console.error('数据库连接失败:', error);
    return false;
  }
};

// 执行查询
export const query = async (sql: string, params?: any[]): Promise<any> => {
  try {
    const [rows] = await pool.execute(sql, params);
    return rows;
  } catch (error) {
    console.error('查询执行失败:', error);
    throw error;
  }
};

// 开始事务
export const beginTransaction = async (): Promise<mysql.PoolConnection> => {
  const connection = await pool.getConnection();
  await connection.beginTransaction();
  return connection;
};

// 提交事务
export const commitTransaction = async (connection: mysql.PoolConnection): Promise<void> => {
  try {
    await connection.commit();
  } finally {
    connection.release();
  }
};

// 回滚事务
export const rollbackTransaction = async (connection: mysql.PoolConnection): Promise<void> => {
  try {
    await connection.rollback();
  } finally {
    connection.release();
  }
};

// 初始化数据库
export const initializeDatabase = async (): Promise<void> => {
  try {
    await testConnection();
  } catch (error) {
    console.error('Failed to initialize database:', error);
    throw error;
  }
};

// 关闭数据库连接
export const closeDatabase = async (): Promise<void> => {
  await pool.end();
};

// 关闭连接池（保留向后兼容）
export const closePool = async (): Promise<void> => {
  await pool.end();
};

export default pool;